# db-asterisk.pm
# Copyright (C) 2006  Stephane Alnet
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 3
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
# 

#
# For more information visit http://carrierclass.net/
#
package CCNCore::Provision::Database::db_asterisk;
use strict; use warnings;
use CCNCore::Provision::Base;
use base qw(CCNCore::Provision::Database);
use CCN::Text;
use CCNCore::Scripting;
use CCN::Scripting::sql;
use CCNCore::Home;

sub model_name() { return 'asterisk' };
sub get_db_names() { return shift->p->get_cp_db_names }

sub grants($)
{
    our $self = shift;
    our $c    = shift;

    our $SALT = $self->system_salt;

    $self->sql_do_for(<<'SQL',$self->p->{db_member},
        SELECT id FROM cp_clusters WHERE db_cluster = ?
SQL
    sub
    {
        my $CLUSTER = shift;
        my $DBNAME  = 'db_cp_'.$CLUSTER;

        my $GRANTS = '';

        $self->sql_do_for(<<'SQL',$CLUSTER,
            SELECT id FROM server WHERE cp_member = ?
SQL
        sub
        {
            my $CLIENT_pk = shift;
            my $CLIENT_IP = $self->get_replication_ip_of($CLIENT_pk);

            # See asterisk_realtime.pm,
            my $PASSWORD = $self->make_password($self->p->{db_member},${SALT},${CLIENT_pk},"asterisk");

            $GRANTS .= <<"SQL";
GRANT ALL PRIVILEGES ON ${DBNAME}.* TO asterisk\@${CLIENT_IP} IDENTIFIED BY '${PASSWORD}';
SQL
        });

        foreach(CCNCore::Home::config."/local/bin/grants-asterisk-${CLUSTER}.sql")
        {
            print_to $c->root().$_, ${GRANTS};
            $c->commit_file($_);

            $self->log(ref($self)."::grants: run_command $DBNAME $_\n"),
            CCN::Scripting::run_command(CCN::Scripting::sql::SQL_FILE(${DBNAME}, $_))
                if $self->has_changed() or $c->changed($_);
        };

    }); # foreach @cp_clusters
    
}


# TBD This version is too blunt (it will overwrite everything every time).
# Need to flag when the database should be re-created, and also mark which records need to be inserted / updated / deleted from voicemail.

sub overwrite()
{
    my $self = shift;
    my $c = shift;

    # This taken from db_phones.pm    
    
    # Add user information for each number in a pool in an enterprise matching the pattern, etc..

    $self->sql_do_for(<<'SQL',$self->p->{db_member},
        SELECT pk FROM vm_clusters WHERE db_cluster = ?
SQL
    sub
    {
        my $VM = shift;

        # Compare with realtime.pm
        # my $DB_NAME = $self->sql_get(q(SELECT db_name FROM db_names WHERE type = 'cp' AND pk = ? ),$VM);

        my $into_db  = new CCNCore::Database::Local($self->p,$VM);

        my $sth_insert = $into_db->{dbh}->prepare(<<'SQL');
INSERT INTO voicemail_users (customer_id, context, mailbox, fullname, tz, attach, saycid, review, "delete", nextaftercmd, hidefromdir )
VALUES                      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
SQL

        # TBD What if there are too many of them?
        # ... shouldn't be the case -- suggest people group them logically

        $self->sql_do_for(<<'SQL',$VM,
            SELECT enterprise, pattern FROM voicemail WHERE cp_cluster = ?
SQL
        sub
        {
            my ($ENTERPRISE,$PATTERN) = shift;
            
            $into_db->sql_begin;

            $into_db->sql_do('DELETE FROM voicemail_users WHERE context = ?',$ENTERPRISE)
                or return $into_db->sql_rollback();

            # Transform the digit pattern into an SQL "LIKE" pattern (extended with underscore, supported by both SQLite3 and MySQL).
            $PATTERN =~ s/X/_/g;

            $self->sql_do_for(<<'SQL',$ENTERPRISE,$PATTERN,
                SELECT pool.pk, pool.timezone, pool.timeformat,
                       "line".ok, "line"."number", "line".label, phone.notinpubdir
                FROM pool, "line", "phone"
                WHERE pool.enterprise = ?
                  AND "line"."number" LIKE ?
                  AND phone.pool = pool.pk AND "line".phone = phone.pk
SQL
            sub
            {
                my ($POOL,$TZ,$timeformat,$LINE_pk,$NUMBER,$LABEL,$HIDEFROMDIR) = shift;

                $TZ = lc($TZ);
                $TZ =~ s/[^\w]/_/g;
                $TZ .= $timeformat || 24;

                $HIDEFROMDIR = (defined $HIDEFROMDIR && $HIDEFROMDIR =~ /^t/) ? 'yes' : 'no';

                #  --  dialout context  -- should be CoS for user
                #  --  callback context '${ENTERPRISE}-ring'

                $sth_insert->execute(
                #           (`customer_id` , `context` ,      `mailbox` ,  `fullname` , `tz` ,  `attach` , `saycid` , `review` , `delete` , `nextaftercmd` , `hidefromdir`)
                             ${LINE_pk},     ${ENTERPRISE},   ${NUMBER},   ${LABEL},    ${TZ},  'no',      'no',      'yes',     'no',      'yes',           ${HIDEFROMDIR}
                             )
                    or return $into_db->sql_rollback();
            }); # foreach number

            # Replace the default password.
            # '1234' is the default if no default is specified.
            my $DEFAULT_PASSWORD = $self->sql_get(q(SELECT vm_password FROM enterprise WHERE pk = ?),$ENTERPRISE);
            $DEFAULT_PASSWORD = '1234' if not defined $DEFAULT_PASSWORD;

            $self->sql_do(<<'SQL',${DEFAULT_PASSWORD},${ENTERPRISE})
UPDATE voicemail_users SET password = ? WHERE context = ? AND password = '0';
SQL
                or return $into_db->sql_rollback();

            $into_db->sql_commit;

        }); # foreach @enterprise
    }); # foreach vm_cluster

    $self->sql_do_for(<<'SQL',$self->p->{db_member},
        SELECT pk, db_name FROM db_names WHERE type = 'cp' AND db_cluster = ?
SQL
    sub
    {
        my $CLUSTER = shift;
        my $DBNAME  = shift; # Unused (OK)

        use CCNCore::Database::Local;
        my $into_db  = new CCNCore::Database::Local($self->p,$CLUSTER);

        if( $into_db->is_connected )
        {
            # Most of the data gets populated here.
            $self->p->populate_into($into_db,CCNCore::Home::manager.'/lib/sql/asterisk.sql');
        }
        else
        {
            $self->die("Database $DBNAME for cluster $CLUSTER did not connect.");
        }

        $self->database_done($DBNAME);
    }); # foreach @cp_clusters

}

1;
